package com.zmj.sy.mom.srv.aps.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zmj.sy.mom.srv.aps.bean.bo.test.TestValidCount;
import com.zmj.sy.mom.srv.aps.bean.entity.order.ApsWorkOrder;
import com.zmj.sy.mom.srv.aps.bean.entity.order.OrderBom;
import com.zmj.sy.mom.srv.aps.bean.vo.nest.workspace.NestPartUnbindListResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.plan.PlanMonthlyProcessDateResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.plan.PlanProDetailDayResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.purchaseplan.ApsWorkOrderPurchasePlanResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.report.ReportProductionOrderResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.report.ReportProductionPartResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.weekwork.PlanDetailDay;
import com.zmj.sy.mom.srv.aps.bean.vo.workshop.WorkshopComponentPartResVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ApsWorkOrderMapper extends SyMapper<ApsWorkOrder> {
    @Select("delete from tb_aps_work_order where order_id = #{id}")
    void deleteOrderId(@Param("id") Integer id);

    @Select("delete from tb_aps_work_order where bom_id = #{id}")
    void deleteBomId(@Param("id") Integer id);

    @Select("select t1.id,\n" +
            "              t2.construct_no, -- 施工号\n" +
            "              t1.work_order_code, -- 工单号\n" +
            "              t1.mater_no, -- 零件编码\n" +
            "              t1.mater_name, -- 零件名称\n" +
            "              t1.drawing, -- 零件图\n" +
            "              null partLength, -- 零件长度\n" +
            "              null partWidth, -- 零件宽度\n" +
            "              t1.mater_high partHeight, -- 零件厚度\n" +
            "              t1.part_count partHeight, -- 单架数量\n" +
            "              t1.total_count totalCount, -- 零件数量\n" +
            "              null packageSn -- 配盘编号\n" +
            "            from tb_aps_work_order t1\n" +
            "              inner join tb_order t2 on t1.order_id=t2.id\n" +
            "              inner join tb_aps_work_order t3 on t3.id=t1.pid\n" +
            "              inner join tb_aps_work_detail t4 on t4.aps_work_order_id=t3.id\n" +
            "            ${ew.customSqlSegment} ")
    List<WorkshopComponentPartResVo> listComponentPart(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> query);


    @Select(" select ob.id id, ob.plm_id plmId, ob.standards spec, ob.drawing drawing " +
            " from tb_order_bom ob " +
            " left join tb_order o on ob.order_id = o.id " +
            " ${ew.customSqlSegment}  ")
    Page<ReportProductionPartResVo> productionPart(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> query, Page<ApsWorkOrder> page);

    @Select(" select awo.id id, o.serial_code serialCode, o.customer_name customerName, ao.seq pkgCode,  ob.drawing drawing, ob.plm_id plmId, ob.mater_name materName, ob.standards spec,  awo.plan_start_date_time planStartDateTime, awo.order_count orderCount, awo.finish_count finishCount, obp.plm_id parentMaterCode " +
            " from tb_aps_work_order awo  " +
            " left join tb_order_bom ob on awo.bom_id = ob.id  " +
            " left join tb_order_bom obp on obp.id = awo.bom_pid " +
            " left join tb_aps_order ao on awo.aps_order_id = ao.id " +
            " left join tb_order o on awo.order_id = o.id " +
            " ${ew.customSqlSegment} ")
    Page<ReportProductionOrderResVo> productionOrder(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> wrapper, Page<ApsWorkOrder> page);
    /**
     * 套料报表-零件列表（添加新查询条件）
     *
     */
    @Select("  SELECT tsp.part_id plmId,tsp.part_name,tsp.texture spec,tsp.thick_ness,tsp.version_num,tsp.create_username,tsp.create_date_time,tsp.modify_username,tsp.modify_date_time , " +
            " np.width ,np.length,np.thickness,np.area,np.draw_status partMapUrl, np.png_url pngUrl, np.work_chain  " +
            " FROM tb_third_sinocam_pushdrawing tsp " +
            " LEFT JOIN tb_order_part np on np.plm_id = tsp.part_id " +
            " ${ew.customSqlSegment}")
    Page<ReportProductionPartResVo> productionPartPost(@Param(Constants.WRAPPER)QueryWrapper<ApsWorkOrder> query, Page<Object> page);
    /**
     * @Description: 套料报表-订单列表(添加新查询条件)
     *
     * @author: hejiahui
     * @since: 2024/10/26 16:55
     */
    @Select(" select awd.aps_work_order_id id, o.serial_code serialCode, o.customer_name customerName, spp.processing_no pkgCode, spp.part_id plmId, spp.part_name materName, spd.texture spec, spd.thick_ness thickness, op.length length, op.width width, awd.group_code groupCode, " +
            " spp.plan_amount orderCount, spp.plan_finish_amount nestCount , spd.file_path filePath, spp.nest_plan_id nestPlanId, " +
            " spp.need_date planStartDateTime, spp.component_id parentPlmId, spp.product_id productCode, op.area area, op.work_chain, op.png_url pngUrl, spp.replace_texture replaceTexture, spp.push_type pushType " +
            " from tb_third_sinocam_pushplan spp " +
            " left join tb_third_sinocam_pushdrawing spd on spd.uni_mater_code = spp.uni_mater_code  " +
            " left join tb_aps_work_detail awd on spp.nest_plan_id = awd.id  " +
            " left join tb_order_part op on op.plm_id = spp.part_id " +
            " left join tb_order o on spp.order_id = o.id " +
            " ${ew.customSqlSegment} "
    )
    Page<ReportProductionOrderResVo> productionOrderPost(@Param(Constants.WRAPPER)QueryWrapper<ApsWorkOrder> query, Page<Object> page);

    @Select(" select awd.aps_work_order_id id, o.serial_code serialCode, o.customer_name customerName, spp.processing_no pkgCode, spp.part_id plmId, spp.part_name materName, spp.replace_texture spec, spp.replace_thickness thickness, op.length length, op.width width, awd.group_code groupCode, " +
            " spp.plan_amount orderCount, spp.plan_finish_amount nestCount , (spp.plan_amount - spp.plan_finish_amount) remainingCount, spd.file_path filePath, spp.nest_plan_id nestPlanId, " +
            " spp.need_date planStartDateTime, spp.component_id parentPlmId, spp.product_id productCode, op.area area, op.work_chain, op.png_url pngUrl, spp.replace_texture replaceTexture," +
            " nsp.create_username nestUserCode " +
            " from tb_third_sinocam_pushplan spp " +
            " left join tb_third_sinocam_pushdrawing spd on spd.uni_mater_code = spp.uni_mater_code  " +
            " left join tb_aps_work_detail awd on spp.nest_plan_id = awd.id  " +
            " left join tb_order_part op on op.plm_id = spp.part_id " +
            " left join tb_order o on spp.order_id = o.id " +
            " left join tb_nest_space_part nsp on nsp.part_plan_id = spp.id and nsp.status in (1,2) " +
            " ${ew.customSqlSegment} "
    )
    Page<NestPartUnbindListResVo> productionOrderPost2(@Param(Constants.WRAPPER)QueryWrapper<ApsWorkOrder> query, Page<Object> page);


    @Select(" SELECT o.construct_no, o.mater_code componentCode, awo.drawing paintCode, o.mater_name componentName, tob.plm_id parentPlmId, awo.plm_id, awo.mater_name , awo.standards, sum(awo.order_count) demandCount, sum(awo.finish_count) pickCount, min(awo.plan_start_date_time) deliveryDateTime " +
            " FROM tb_aps_work_order awo " +
            " LEFT JOIN tb_order o ON awo.order_id = o.id " +
            " LEFT JOIN tb_order_bom tob ON awo.bom_pid = tob.id " +
            " ${ew.customSqlSegment} ")
    Page<ApsWorkOrderPurchasePlanResVo> purchasePlan(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> wrapper, Page<Object> page);

    @Select("SELECT awo.mater_code AS plmId, awo.mater_name, sum( awo.order_count ) demandCount, tob.id tobId " +
            "FROM tb_aps_work_order awo LEFT JOIN tb_order o ON awo.order_id = o.id LEFT JOIN tb_order_bom tob ON awo.bom_pid = tob.id " +
            " ${ew.customSqlSegment} ")
    List<ApsWorkOrderPurchasePlanResVo> exportExcel(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> wrapper);

    @Select(" select sum(awo.order_count) orderCount , sum(pp.plan_finish_amount) finishCount " +
            " from tb_aps_work_order awo " +
            " left join tb_aps_work_detail awd on awd.aps_work_order_id =  awo.id and awd.phase_code in ('JG', 'HG') " +
            " left join tb_third_sinocam_pushplan pp on pp.nest_plan_id = awd.id " +
            " where awo.bom_id = #{bomId}")
    ApsWorkOrder sumCount(@Param("bomId") Integer bomId);

    @Select("select * from tb_order_bom where id not in (select distinct bom_id from tb_aps_work_order where order_id = #{orderId} ) and order_id = #{orderId} and plm_id not like '010%' and plm_id not like '020%' and plm_id not like '050%' ")
    List<OrderBom> notInBom(@Param("orderId") Integer id);

    @Select(" select awd.aps_work_order_id apsWorkOrderId, awd.phase_code phaseCode, awd.phase_seq phaseSeq, awo.order_count orderCount, awd.total_count totalCount, awd.finish_count finishCount " +
            " from tb_aps_work_detail awd  " +
            " left join tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " where awd.order_id = #{orderId} and phase_code in ('YC', 'JG', 'LL') and type = 2 ")
    List<TestValidCount> findAllPhaseCount(@Param("orderId") Integer orderId);

    @Select("SELECT awo.order_count AS count FROM tb_aps_work_order awo ${ew.customSqlSegment} ")
    PlanDetailDay getDayPlanDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> query);

    @Select(" SELECT awo.order_id, awo.bom_id, awo.bom_pid, awo.plm_id plmId, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') AS dateStr, sum(awd.total_count) planCount, max(awo.part_count) partCount, max(awo.total_count) parentPartCount " +
            " FROM tb_aps_work_detail awd  " +
            " left join tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " join ( " +
            " select aps_work_order_id , max(phase_seq) phase_seq from tb_aps_work_detail where order_id in (${orderId}) group by aps_work_order_id " +
            " ) ph on ph.aps_work_order_id = awd.aps_work_order_id and ph.phase_seq = awd.phase_seq " +
            " ${ew.customSqlSegment} ")
    List<PlanProDetailDayResVo> partsAssemblyArea(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> assemblyWrapper, @Param("orderId") String orderId);

    @Select(" SELECT awo.order_id, awo.bom_id, awo.bom_pid, awo.plm_id plmId,  DATE_FORMAT(DATE_SUB(wdt.create_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') AS finishDateStr, 0 planCount, SUM(wdt.success_count) AS finishCount " +
            " FROM tb_mes_work_record wdt " +
            " LEFT JOIN tb_aps_work_detail awd  ON wdt.aps_work_detail_id = awd.id " +
            " LEFT JOIN tb_aps_work_order awo  ON awd.aps_work_order_id = awo.id " +
            " JOIN ( " +
            "     SELECT aps_work_order_id, MAX(phase_seq) AS phase_seq FROM tb_aps_work_detail WHERE order_id IN (${orderId}) GROUP BY aps_work_order_id " +
            " ) ph ON ph.aps_work_order_id = awd.aps_work_order_id  and ph.phase_seq = awd.phase_seq " +
            " ${ew.customSqlSegment} ")
    List<PlanProDetailDayResVo> partsAssemblyAreaFinish(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> assemblyWrapper, @Param("orderId") String orderId);



    @Select(" SELECT awo.order_id, awo.bom_id, awo.bom_pid, awo.plm_id plmId, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') AS dateStr, sum(awd.total_count) totalCount, max(awo.part_count) partCount, max(awo.total_count) parentPartCount " +
            " FROM tb_aps_work_detail awd  " +
            " left join tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " join ( " +
            " select aps_work_order_id , max(phase_seq) phase_seq from tb_aps_work_detail where order_id in (${orderId}) group by aps_work_order_id " +
            " ) ph on ph.aps_work_order_id = awd.aps_work_order_id and ph.phase_seq = awd.phase_seq " +
            " left join tb_aps_work_order awo2 on awo2.id = awd.aps_work_order_pid " +
            " ${ew.customSqlSegment} ")
    List<PlanMonthlyProcessDateResVo> partsAssemblyAreaMbd(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkOrder> assemblyWrapper, @Param("orderId") String orderId);
}
